<html>
    <head>
        <title>Test CRM  Report</title>

    </head>
    <body>
     
                <?php
                define('FPDF_FONTPATH', './fpdf/font/');
                require_once("fpdf/fpdf.php");    
             

                class PDF extends FPDF {
                    
                    function LoadData($file) {
                        //Read file lines
                        $lines = file($file);
                        $data = array();
                        foreach ($lines as $line)
                            $data[] = explode(';', chop($line));
                        return $data;
                    }
                    
                    function BasicTable($data, $data1,$results) {
                        $head = array('','TODAY SALES', 'MTD', 'QTD', 'YTD');
                        $ss = array( 143,33, 33, 33, 33);
                        $header = array('NO.', 'ACCOUNT', 'SALES DIVISION', 'SALES DEPARTMENT', 'LEVEL OF RELATIONSHIP', 'GRADE', 'LOCATION NAME',
                            'TERRITORY', 'SELL-IN', 'SELL-OUT', 'VARIANCE', 'SELL-IN', 'SELL-OUT', 'VARIANCE', 'SELL-IN', 'SELL-OUT', 'VARIANCE', 'SELL-IN', 'SELL-OUT', 'VARIANCE');
                        $w = array(5, 20, 20, 25, 25, 15, 15, 18, 11, 11,11, 11, 11, 11, 11, 11,11, 11, 11, 11, 11, 11);
                        echo "<br/>";
                        //print_r($data);
                       // print_r(data1);
                       // $this->SetX(153);
                        for ($i = 0; $i < count($head); $i++)
                            $this->Cell($ss[$i], 5, iconv('UTF-8', 'TIS-620', $head[$i]), 1, 0, 'C'); //หัววคอลัมท่ี2
                        $this->Ln();
                        for ($i = 0; $i < count($header); $i++)
                            $this->Cell($w[$i], 5, iconv('UTF-8', 'TIS-620', $header[$i]), 1, 0, 'C');//เซตหัวข้อเป็นไทย
                        $this->Ln();
                     
                        foreach ($results as $eachResult) {
                          $this->Cell(5, 6, $row, 1, 0, 'C');
                            $this->Cell(20, 6, $eachResult["name"], 1, 0, 'C');
                              
                        }
                        foreach ($data1 as $obj) {
                            $this->setX(35);
                            $this->Cell(20, 6, $obj["sellingdivision_c"], 1, 0, 'C');
                            $this->Cell(25, 6, $obj["sellingdepartmentbranch_c"], 1, 0, 'C');
                            
                            // $this->Cell(34,6,  $obj["netamount_c"],1,0,'C');
                            $this->Ln();
                        }
                       
                        foreach ($data as $result) {
                             $this->SetXY(80,20);
                            $this->Cell(25, 6, $result["levelofrelationship_c"], 1, 0, 'C');
                            $this->Cell(15, 6, $result["grade_c"], 1, 0, 'C');
                            $this->Cell(15, 6, 'Unknow',1,0,'C');
                            $this->Cell(18, 6, $result["territory_c"], 1, 0, 'C');
                      
                           // $this->Ln();
                        }
                        
                        foreach ($data1 as $data1) {
                            $this->SetX(-144);
                             $this->Cell(11,6,  $data1["netamount_c"],1,0,'C');
                             $this->Cell(11,6,'0',1,0,'C');//ถ้าได้ใช้งานก็ค่อยเข้ามาเปลี่ยน 0 ให้เป็นตัวแปรอื่น
                              $res = $data1["netamount_c"]-0;
                          
                
                          
                              $this->Ln();
                        }
                    }

                }

                Class getdata{

                function connectdb() {
                    $objConnect = mysql_connect("localhost", "root", "root") or die("Error Connect to Database"); //
                    $objDB = mysql_select_db("proto2point1");
                    $strSQL = "SELECT parent_id from accounts_audit where created_by IN (SELECT user_id FROM tracker WHERE item_summary IN  (
                SELECT `name`FROM  sao_salesorder JOIN sao_salesorder_cstm ON sao_salesorder_cstm.id_c =  sao_salesorder.id 
                WHERE  sao_salesorder_cstm.sellingdivision_c = '0') and action = 'detailview')";
                    $objQuery = mysql_query($strSQL) or die("Error Query [" . $strSQL . "]");
                    $row = mysql_num_rows($objQuery);
                    $resultData = array();
                    for ($i = 0; $i < mysql_num_rows($objQuery); $i++) {
                        $result = mysql_fetch_array($objQuery);
                        array_push($resultData, $result);
                    }
                    return $resultData;
                }

                function outputdata($resultData,$pdf) {
                    $row = 0;
                    foreach ($this->connectdb() as $data) {
                        $rows++; //นับบแถว
                        $strSQL = "SELECT id,name FROM accounts WHERE 'deleted' = 0 and id IN(SELECT id_c FROM  accounts_cstm WHERE id_c  = '{$data["parent_id"]}')";
                        $objQuery = mysql_query($strSQL);
                        mysql_query("SET NAMES utf8");
                        $results = array();
                        for ($i = 0; $i < mysql_num_rows($objQuery); $i++) {
                            $result = mysql_fetch_array($objQuery);
                            array_push($results, $result);
                        }
                        $strSQL = "SELECT id_c,grade_c,salesterritory_c,levelofrelationship_c FROM  accounts_cstm WHERE id_c  = '" . $results[0]['id'] . "'";
                        $objQuery = mysql_query($strSQL);
                        mysql_query("SET NAMES utf8");
                        $re = array();
                        for ($i = 0; $i < mysql_num_rows($objQuery); $i++) {
                            $result = mysql_fetch_array($objQuery);
                            array_push($re, $result);
                        }
                        $strSQL = "SELECT distinct sellingdepartmentbranch_c,sellingdivision_c,netamount_c from sao_salesorder_cstm WHERE id_c IN (
                    SELECT  id from sao_salesorder WHERE `name` IN (
                    SELECT item_summary FROM tracker WHERE module_name = 'sao_salesorder' and user_id IN(
                    SELECT   created_by from accounts_audit where parent_id IN (
                    SELECT id from accounts WHERE id = '{$re[0]["id_c"]}')))) ";
                        $objQuery = mysql_query($strSQL);
                        mysql_query("SET NAMES utf8");
                        $re1 = array();
                        for ($i = 0; $i < mysql_num_rows($objQuery); $i++) {
                            $result = mysql_fetch_array($objQuery);
                            array_push($re1, $result);
                        }
                        $pdf->AddFont('tahoma', '', 'tahoma.php'); //เพิ่มอักษรไทย
                        $pdf->AddFont('tahoma', 'B', 'tahoma.php'); //เพิ่มอักษรไทย
                        $pdf->SetFont('tahoma', '', 5);
                        $pdf->BasicTable($re, $re1,$results);
                        $pdf->AddPage('L', 'A4');
                    }


                   
                }
                }
                
                $pdf = new PDF();
                $getdata = new getdata();
                $getdata->connectdb();
                $getdata->outputdata($resultData,$pdf); 
                $pdf->Output('pdf/apr.pdf');
                echo "success";
             
                ?>

    </body>
</html>